# coding: utf-8
from datetime import datetime
from django.db import models
from lib.common import BasicManager
from django.core.paginator import Paginator, EmptyPage, PageNotAnInteger
import json
from services.datamodel.student.models import (Student)
from django.db.models import Sum, Count, Q, F, Min, Max, Case, Value, When, CharField
from datetime import datetime, date, timedelta
import calendar
import collections
from django.db import connection
from django.core.cache import cache

class ModelManagerNote(BasicManager):

    taskType = 2

    def get_list(self, page, num=50, nickname=None, types=None, start_date=None, end_date=None, user_id=None,
                 status=None, export=None, types_sort=None, pv_sort=None, share_sort=None, message_sort=None, like_sort=None,
                 status_sort=None, like_u=None):
        result = self.values("content", "created_at", "like", "share", "message", "private", "status", "duration",
                             "student__nick_name", "student__id", "id", "student__avatar", "imgs",
                             "types", "student__title").order_by("-created_at")
        if export:
            from io import BytesIO
            from django.http import HttpResponse
            import xlwt
            ws = xlwt.Workbook(encoding="UTF-8")
            w = ws.add_sheet(u'数据')
            w.write(0, 0, u'用户编号')
            w.write(0, 1, u'用户昵称')
            w.write(0, 2, u'笔记内容')
            w.write(0, 3, u'专区')
            w.write(0, 4, u'笔记发布时间')
            w.write(0, 5, u'转发次数')
            w.write(0, 6, u'评论次数')
            w.write(0, 7, u'点赞次数')
            w.write(0, 8, u'是否私密')
            excel_row = 1
            for obj in result:
                w.write(excel_row, 0, obj['student__id'])
                w.write(excel_row, 1, obj['student__nick_name'])
                w.write(excel_row, 2, obj['content'])
                w.write(excel_row, 3, obj['types'])
                w.write(excel_row, 4, obj['created_at'].strftime('%Y-%m-%d'))
                w.write(excel_row, 5, obj['share'])
                w.write(excel_row, 6, obj['message'])
                w.write(excel_row, 7, obj['like'])
                w.write(excel_row, 8, obj['status'])
                excel_row += 1
            sio = BytesIO()
            ws.save(sio)
            sio.seek(0)
            response = HttpResponse(sio.getvalue(), content_type='application/vnd.ms-excel')
            response['Content-Disposition'] = 'attachment; filename=stu_info.xls'
            response.write(sio.getvalue())
            return response
        if nickname:
            result = result.filter(student__nick_name=nickname)
        if types and int(types) in [0, 1]:
            result = result.filter(types=types)
        if start_date and end_date:
            result = result.filter(created_at__range=[start_date, end_date])
        if user_id:
            result = result.filter(student_id=user_id)
        if status != '' and int(status) in [0, 1, 2]:
            result = result.filter(status=status)
        if types_sort and int(types_sort) in [0, 1]:
            if int(types_sort) == 0:
                result = result.order_by("types")
            if int(types_sort) == 1:
                result = result.order_by("-types")
        if pv_sort and int(pv_sort) in [0, 1]:
            if int(pv_sort) == 0:
                result = result.order_by("pv")
            if int(pv_sort) == 1:
                result = result.order_by("-pv")
        if share_sort and int(share_sort) in [0, 1]:
            if int(share_sort) == 0:
                result = result.order_by("share")
            if int(share_sort) == 1:
                result = result.order_by("-share")
        if message_sort and int(message_sort) in [0, 1]:
            if int(message_sort) == 0:
                result = result.order_by("message")
            if int(message_sort) == 1:
                result = result.order_by("-message")
        if like_sort and int(like_sort) in [0, 1]:
            if int(like_sort) == 0:
                result = result.order_by("like")
            if int(like_sort) == 1:
                result = result.order_by("-like")
        if status_sort and int(status_sort) in [0, 1]:
            if int(status_sort) == 0:
                result = result.order_by("status")
            if int(status_sort) == 1:
                result = result.order_by("-status")
        result = result.filter(types__in=[0,1])
        paginator = Paginator(result, num)
        count = result.aggregate(count=Count('id'))
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage as e:
            return {'retlist': [], 'curpageidx': page,
                    'hasnext': False, 'hasprevious': False}
        if like_u:
            for v in result_page:
                like = mm_NoteMessage.get_or_default(student_id=like_u, note_id=v['id'], types=1, pid=0)
                if like:
                    v['is_like'] = 1
                    v['like_status'] = like.status
                else:
                    v['is_like'] = 0
        return {'retlist': list(result_page), 'curpageidx': page, 'hasnext': result_page.has_next(),
                'hasprevious': result_page.has_previous(), 'total_page': paginator.num_pages, 'count': count['count']}


class ModelManagerNoteMessage(BasicManager):
    def get_list(self, page, num=50, nickname=None, user_id=None, note_id=None, times=None, like=None, uid=None):
        result = self.filter(types=0).values("content", "created_at", "student__nick_name", "student__id",
                                             "student__avatar", "id", 'like', 'note_id').order_by("-created_at")
        if nickname:
            result = result.filter(student__nick_name=nickname)
        if user_id:
            result = result.filter(student__id=user_id)
        if note_id:
            result = result.filter(note_id=note_id)
        if int(times):
            result = result.order_by("-created_at")
        if int(like):
            result = result.order_by("-like", "-created_at")
        paginator = Paginator(result, num)
        count = result.aggregate(count=Count('id'))
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage as e:
            return {'retlist': [], 'curpageidx': page,
                    'hasnext': False, 'hasprevious': False}
        if uid:
            for v in result_page:
                like = mm_NoteMessage.get_or_default(student_id=uid, note_id=v['note_id'], types=1, pid=v['id'])
                if like:
                    v['is_like'] = 1
                    v['like_status'] = like.status
                else:
                    v['is_like'] = 0
        return {'retlist': list(result_page), 'curpageidx': page, 'hasnext': result_page.has_next(),
                'hasprevious': result_page.has_previous(), 'total_page': paginator.num_pages, 'count': count['count']}

    def get_unread_message(self, page, user_id):
        result = self.filter(Q(note__student_id=user_id) | Q(Q(student_id=user_id) & Q(types=1) & ~Q(pid=0))).values("content", "student__nick_name", "status",
                                                              "student__avatar", "created_at", "note_id", "types",
                                                              "id", "note__imgs", "note__content", "pid").order_by("-created_at")
        paginator = Paginator(result, 10)
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage as e:
            return {'retlist': [], 'curpageidx': page,
                    'hasnext': False, 'hasprevious': False}
        pid_list = []
        for v in result_page:
            self.filter(id=v['id']).update(status=1)
            if v['pid']:
                pid_list.append(v.get('pid'))
        parents = self.filter(id__in=pid_list).values("content", "id")
        for parent in parents:
            for row in result_page:
                if row['pid'] == parent['id']:
                    row['pid__content'] = parent['content']

        return {'retlist': list(result_page), 'curpageidx': page, 'hasnext': result_page.has_next(),
                'hasprevious': result_page.has_previous(), 'total_page': paginator.num_pages, 'count': paginator.count}


class ModelManagerNoteLike(BasicManager):
    pass


class ModelManagerFamousSentences(BasicManager):

    def get_list(self, page, num=50):
        result = self.values("content_one", "created_at", "name", "id", "content_two").order_by("-created_at")
        paginator = Paginator(result, num)
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage as e:
            return {'retlist': [], 'curpageidx': page,
                    'hasnext': False, 'hasprevious': False}
        return {'retlist': list(result_page), 'curpageidx': page, 'hasnext': result_page.has_next(),
                'hasprevious': result_page.has_previous(), 'total_page': paginator.num_pages}


class ModelManagerShareParameter(BasicManager):
    def get_list(self, page, num):
        result = self.values().order_by("-created_at")
        paginator = Paginator(result, num)
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage as e:
            return {'retlist': [], 'curpageidx': page,
                    'hasnext': False, 'hasprevious': False}
        return {'retlist': list(result_page), 'curpageidx': page, 'hasnext': result_page.has_next(),
                'hasprevious': result_page.has_previous(), 'total_page': paginator.num_pages, 'count': paginator.count}


class ModelManagerAllData(BasicManager):

    def get_list(self, page, num=50, start_date=None, end_date=None, export=None):
        result = self.values().order_by("-created_at")
        if start_date and end_date:
            result = result.filter(created_at__range=[start_date, end_date])
        if export:
            from io import BytesIO
            from django.http import HttpResponse
            import xlwt
            ws = xlwt.Workbook(encoding="UTF-8")
            w = ws.add_sheet(u'数据')
            if int(export) == 1:
                w.write(0, 0, u'专注人数')
                w.write(0, 1, u'专注率')
                w.write(0, 2, u'专注次数')
                w.write(0, 3, u'专注笔记数量')
                w.write(0, 4, u'转发次数')
                w.write(0, 5, u'评论次数')
                w.write(0, 6, u'点赞数量')
                w.write(0, 7, u'日期')
                excel_row = 1
                for obj in result:
                    w.write(excel_row, 0, obj['absorbed_number_num'])
                    w.write(excel_row, 1, '' if not int(obj['uv']) else round(obj['absorbed_number_num']/obj['uv'], 2))
                    w.write(excel_row, 2, obj['absorbed_num'])
                    w.write(excel_row, 3, obj['absorbed_biji_num'])
                    w.write(excel_row, 4, obj['forward_num'])
                    w.write(excel_row, 5, obj['comment_num'])
                    w.write(excel_row, 6, obj['like_num'])
                    w.write(excel_row, 7, obj['created_at'].strftime('%Y-%m-%d'))
                    excel_row += 1
            if int(export) == 2:
                w.write(0, 0, u'生成海报数')
                w.write(0, 1, u'海报扫码数')
                w.write(0, 2, u'小程序分享数')
                w.write(0, 3, u'职场锦囊点击')
                w.write(0, 4, u'排行榜点击')
                w.write(0, 5, u'日期')
                excel_row = 1
                for obj in result:
                    w.write(excel_row, 0, obj['poster_num'])
                    w.write(excel_row, 1, obj['poster_saoma_num'])
                    w.write(excel_row, 2, obj['applet_share_num'])
                    w.write(excel_row, 3, obj['workplace_silkbag_num'])
                    w.write(excel_row, 4, obj['ranking_num'])
                    w.write(excel_row, 5, obj['created_at'].strftime('%Y-%m-%d'))
                    excel_row += 1
            sio = BytesIO()
            ws.save(sio)
            sio.seek(0)
            response = HttpResponse(sio.getvalue(), content_type='application/vnd.ms-excel')
            response['Content-Disposition'] = 'attachment; filename=stu_info.xls'
            response.write(sio.getvalue())
            return response
        paginator = Paginator(result, num)
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage as e:
            return {'retlist': [], 'curpageidx': page,
                    'hasnext': False, 'hasprevious': False}
        return {'retlist': list(result_page), 'curpageidx': page, 'hasnext': result_page.has_next(),
                'hasprevious': result_page.has_previous(), 'total_page': paginator.num_pages}

    def basics_analysis(self, page, num=50, start_date=None, end_date=None, export=None):
        result = self.values("uv", "pv", "number_num", "number_sum", "created_at", "day").order_by("-created_at")
        if start_date and end_date:
            result = result.filter(created_at__range=[start_date, end_date])
        if export:
            pass
        paginator = Paginator(result, num)
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage as e:
            return {'retlist': [], 'curpageidx': page,
                    'hasnext': False, 'hasprevious': False}
        i = 1
        r_list = []
        if not export:
            r_list = list(result_page)
        if export:
            r_list = list(result)
        count = len(r_list)
        for v in r_list:
            tomorrow = self.get_oneday(2, v['day'])
            three = self.get_oneday(4, v['day'])
            seven = self.get_oneday(8, v['day'])
            # 获取当天的新用户 和 其他日之后的
            new_number = cache.get(v['day'])
            tomorrow_number = cache.get(str(tomorrow)+"-a") if cache.get(str(tomorrow)+"-a") else "[]"
            three_number = cache.get(str(three)+"-a") if cache.get(str(three)+"-a") else "[]"
            seven_number = cache.get(str(seven)+"-a") if cache.get(str(seven)+"-a") else "[]"
            if new_number:
                intersection_tomorrow = list(set(json.loads(new_number)).intersection(set(json.loads(tomorrow_number))))
                intersection_three = list(set(json.loads(new_number)).intersection(set(json.loads(three_number))))
                intersection_seven = list(set(json.loads(new_number)).intersection(set(json.loads(seven_number))))
                v['tomorrow_proportion'] = str(round(len(intersection_tomorrow)/v['number_num'], 2)*100)+"%"
                v['three_proportion'] = str(round(len(intersection_three)/v['number_num'], 2)*100)+"%"
                v['seven_proportion'] = str(round(len(intersection_seven)/v['number_num'], 2)*100)+"%"
            else:
                v['tomorrow_proportion'] = "0%"
                v['three_proportion'] = "0%"
                v['seven_proportion'] = "0%"
            if i == count:
                v['increase'] = 0
                continue
            v['increase'] = v['uv']-r_list[i]['uv']
            i = i + 1
        if export:
            from io import BytesIO
            from django.http import HttpResponse
            import xlwt
            ws = xlwt.Workbook(encoding="UTF-8")
            w = ws.add_sheet(u'数据')
            w.write(0, 0, u'UV')
            w.write(0, 1, u'PV')
            w.write(0, 2, u'UV涨幅')
            w.write(0, 3, u'总用户数')
            w.write(0, 4, u'新增用户数')
            w.write(0, 5, u'次日留存率')
            w.write(0, 6, u'3日留存率')
            w.write(0, 7, u'7日留存率')
            excel_row = 1
            for obj in r_list:
                w.write(excel_row, 0, obj['uv'])
                w.write(excel_row, 1, obj['pv'])
                w.write(excel_row, 2, obj['increase'])
                w.write(excel_row, 3, obj['number_sum'])
                w.write(excel_row, 4, obj['number_num'])
                w.write(excel_row, 5, obj['tomorrow_proportion'])
                w.write(excel_row, 6, obj['three_proportion'])
                w.write(excel_row, 7, obj['seven_proportion'])
                excel_row += 1
            sio = BytesIO()
            ws.save(sio)
            sio.seek(0)
            response = HttpResponse(sio.getvalue(), content_type='application/vnd.ms-excel')
            response['Content-Disposition'] = 'attachment; filename=stu_info.xls'
            response.write(sio.getvalue())
            return response
        return {'retlist': r_list, 'curpageidx': page, 'hasnext': result_page.has_next(),
                'hasprevious': result_page.has_previous(), 'total_page': paginator.num_pages}

    # def update_message_num(self):
    #     today = date.today()
    #     res = self.get_or_default(day=today)
    #     if not res:
    #         self.create(comment_num=1)
    #         return True
    #     res.comment_num=res.comment_num+1
    #     res.save()
    #
    # def update_like_num(self):
    #     today = date.today()
    #     res = self.get_or_default(day=today)
    #     if not res:
    #         self.create(like_num=1)
    #         return True
    #     res.like_num=res.like_num+1
    #     res.save()

    def update_all_num(self, num):
        today = date.today()
        name = {1: 'comment_num', 2: 'like_num', 3: 'absorbed_biji_num', 4: 'absorbed_num', 5: 'absorbed_number_num',
                6: 'ranking_num', 7: 'workplace_silkbag_num', 8: 'applet_share_num', 9: 'poster_saoma_num',
                10: 'poster_num', 11: 'pv', 12: 'uv', 13: 'number_num', 14: 'forward_num'}
        result = self.filter(day=today).values().first()
        num = int(num)
        if result:
            with connection.cursor() as cur:
                cur.execute("update momself_all_data set "+name[num]+"="+name[num]+"+1 where id="+str(result['id'])+";")
        else:
            if num == 1:
                self.create(comment_num=1)
            if num == 2:
                self.create(like_num=1)
            if num == 3:
                self.create(absorbed_biji_num=1)
            if num == 4:
                self.create(absorbed_num=1)
            if num == 5:
                self.create(absorbed_number_num=1)
            if num == 6:
                self.create(ranking_num=1)
            if num == 7:
                self.create(workplace_silkbag_num=1)
            if num == 8:
                self.create(applet_share_num=1)
            if num == 9:
                self.create(poster_saoma_num=1)
            if num == 10:
                self.create(poster_num=1)
            if num == 11:
                self.create(pv=1)
            if num == 12:
                self.create(uv=1)
            if num == 13:
                self.create(number_num=1)
            if num == 14:
                self.create(forward_num=1)

    def rest_of_day(self):
        """
        :return: 截止到目前当日剩余时间
        """
        today = datetime.strptime(str(date.today()), "%Y-%m-%d")
        tomorrow = today + timedelta(days=1)
        nowTime = datetime.now()
        return (tomorrow - nowTime).seconds  # 获取秒

    def get_oneday(self, num, day_time):
        oneday = (day_time + timedelta(days=num)).strftime("%Y-%m-%d")
        return oneday


class ModelManagerNoteRanking(BasicManager):

    def get_ranking(self, times):
        result = self.filter(created_at__range=times).values('student_id').annotate(duration=Sum('duration')).values('student__nick_name', 'student__id', 'student__avatar', 'duration').order_by("-duration")[:100]

        ranking_data = []
        for v in result:
            ranking_data.append({'name': v['student__nick_name'], 'duration': v['duration'], 'avatar': v['student__avatar'], 'id': v['student__id']})
        ranking_data.sort(key=lambda k: (k.get('duration', 0)), reverse=True)

        return list(ranking_data)

    def get_my_ranking(self, times, user_id):
        myDuration = self.filter(created_at__range=times, student_id=user_id).aggregate(durationSum=Sum('duration'))
        if myDuration['durationSum'] is None:
            return {'duration': 0, 'num': 0}
        sort = (self.filter(created_at__range=times).values("student_id").annotate(duration_sum=Sum('duration'))).filter(duration_sum__gte=myDuration['durationSum']).count()
        return {'duration': myDuration['durationSum'], 'num': sort}

    def get_user_data(self, user_id):
        """
        获取笔记的专注数据
        :param user_id:
        :return:
        """
        get_cur_month = datetime.now().strftime("%m")
        #  获取当前月的第一天
        today = date.today()
        month_str = datetime.now().strftime('%Y-%m')
        year, month = int(month_str.split('-')[0]), int(month_str.split('-')[1])
        end = calendar.monthrange(year, month)[1]
        last_day = '{}-{}-{}'.format(year, month, end)
        duration_sum = self.filter(student_id=user_id).aggregate(num=Sum("duration"))
        today_duration = self.filter(student_id=user_id, day=today).aggregate(num=Sum("duration"))
        count = mm_Note.filter(student_id=user_id, created_at__range=[str(month_str) + "-01", last_day], types=0).count()
        return {'cur_month': get_cur_month, 'count': count, 'duration_sum': duration_sum['num'],
                'today_duration': today_duration['num']}


class ModelManagerNoteWechatMessage(BasicManager):
    pass


class ModelManagerNoteServersMessage(BasicManager):

    def get_list(self, page, num=50):
        result = self.values("title", "created_at", "rule", "push_sum", "click_sum", "today_clicksum", "status", "id",
                             "today_pushsum").order_by("-created_at")
        paginator = Paginator(result, num)
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage as e:
            return {'retlist': [], 'curpageidx': page,
                    'hasnext': False, 'hasprevious': False}
        return {'retlist': list(result_page), 'curpageidx': page, 'hasnext': result_page.has_next(),
                'hasprevious': result_page.has_previous(), 'total_page': paginator.num_pages}


class ModelManagerNoteBanner(BasicManager):

    def get_list(self, page, num=50):
        result = self.values().order_by("-created_at")
        paginator = Paginator(result, num)
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage as e:
            return {'retlist': [], 'curpageidx': page,
                    'hasnext': False, 'hasprevious': False}
        return {'retlist': list(result_page), 'curpageidx': page, 'hasnext': result_page.has_next(),
                'hasprevious': result_page.has_previous(), 'total_page': paginator.num_pages}


class ModelManagerPlan(BasicManager):
    on_line_status = 1
    off_line_status = 0

    def get_list(self, page, num=50, title=None, start_date=None, end_date=None, status=None, export=None):
        result = self.values("id", "title", "desc", "cycle", "replenish", "original_price", "price", "virtual_num", "img", "bg_img", "code", "status", "drainage_desc", "created_at")\
            .order_by("-created_at")\
            .annotate(stage_count=Count("plan_stage_p__id", distinct=True))\
            .annotate(student_count=Count("plan_clock_in_info_p__student_id", distinct=True))

        if title:
            result = result.filter(title__contains=title)
        if start_date and end_date:
            result = result.filter(created_at__range=[start_date, end_date])
        if status != '' and int(status) in [0, 1]:
            result = result.filter(status=status)
        paginator = Paginator(result, num)
        count = result.aggregate(count=Count('id'))
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage as e:
            return {'retlist': [], 'curpageidx': page,
                    'hasnext': False, 'hasprevious': False}
        if export:
            from io import BytesIO
            from django.http import HttpResponse
            import xlwt
            ws = xlwt.Workbook(encoding="UTF-8")
            w = ws.add_sheet(u'数据')
            w.write(0, 0, u'创建时间')
            w.write(0, 1, u'计划名称')
            w.write(0, 2, u'计划周期')
            w.write(0, 3, u'计划期次')
            w.write(0, 4, u'参与人数')
            w.write(0, 5, u'计划状态')
            excel_row = 1
            for obj in list(result_page):
                w.write(excel_row, 0, obj['created_at'].strftime('%Y-%m-%d'))
                w.write(excel_row, 1, obj['title'])
                w.write(excel_row, 2, obj['cycle'])
                w.write(excel_row, 3, obj['stage_count'])
                w.write(excel_row, 4, obj['student_count'])
                w.write(excel_row, 5, '上架' if obj['status'] else '下架')
                excel_row += 1
            sio = BytesIO()
            ws.save(sio)
            sio.seek(0)
            response = HttpResponse(sio.getvalue(), content_type='application/vnd.ms-excel')
            response['Content-Disposition'] = 'attachment; filename=stu_info.xls'
            response.write(sio.getvalue())
            return response
        return {'retlist': list(result_page), 'curpageidx': page, 'hasnext': result_page.has_next(),
                'hasprevious': result_page.has_previous(), 'total_page': paginator.num_pages, 'count': count['count']}


class ModelManagerPlanStage(BasicManager):
    stage_status_unstart = 0  # 本期未开始
    stage_status_running = 1 #本期进行中
    stage_status_already = 2 #本期已结束

    def get_list(self, page, num=50, plan_id=None, desc=None, start_date=None, end_date=None):
        result = self.values("id", "plan_id", "desc", "all_dates", "start_day", "end_day", "created_at", "updated_at") \
            .annotate(student_num=Count('plan_join_info_ps__student_id', distinct=True)) \
            .annotate(clock_in_num=Count('plan_clock_in_info_ps__student_id', distinct=True)) \
            .order_by("-created_at")
        if plan_id:
            result = result.filter(plan_id=plan_id)
        if desc:
            result = result.filter(desc__contains=desc)
        if start_date and end_date:
            result = result.filter(created_at__range=[start_date, end_date])
        paginator = Paginator(result, num)
        count = result.aggregate(count=Count('id'))
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage as e:
            return {'retlist': [], 'curpageidx': page,
                    'hasnext': False, 'hasprevious': False}

        plan_stage_id_list = list(result_page.object_list.values_list("id", flat=True))
        student_result = mm_PlanClockInInfo.filter(plan_stage_id__in=plan_stage_id_list) \
            .values("plan_stage_id", "student_id").annotate(student_num=Count('student_id')).filter(student_num__gte=F('plan__cycle'))
        for item in result_page:
            item['clock_in_complete_num'] = 0
            for student in student_result:
                if student['plan_stage_id'] == item['id']:
                    item['clock_in_complete_num'] += 1
        return {'retlist': list(result_page), 'curpageidx': page, 'hasnext': result_page.has_next(),
                'hasprevious': result_page.has_previous(), 'total_page': paginator.num_pages, 'count': count['count']}

    def get_stage_task_list(self, page, num=20, plan_stage_id=None, day=None, refine=None, user_id=None, selfStickie=None):
        planStage = mm_PlanStage.get(id=plan_stage_id, plan__status=mm_Plan.on_line_status)
        if planStage is None:
            return {'retlist': [], 'curpageidx': page,
                    'hasnext': False, 'hasprevious': False}
        result = planStage.plan_clock_in_info_ps.annotate(content=F('note__content'), imgs=F('note__imgs')) \
            .values("content", "created_at", "student__nick_name", "student__id", "id", "student__avatar", "imgs", "student__title").order_by("-created_at")
        if day:
            result = result.filter(plan_stage_course__day=day)
        if refine:
            result = result.filter(refine=refine)
        if user_id:
            result = result.filter(student_id=user_id)
        if selfStickie:
            result = result.annotate(selfStickie=Case(When(student_id=selfStickie, then=1), default=0, output_field=CharField())).order_by("-selfStickie", "-created_at")
        paginator = Paginator(result, num)
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage as e:
            return {'retlist': [], 'curpageidx': page,
                    'hasnext': False, 'hasprevious': False}
        return {'retlist': list(result_page), 'curpageidx': page, 'hasnext': result_page.has_next(),
                'hasprevious': result_page.has_previous(), 'total_page': paginator.num_pages}


class ModelManagerPlanCourse(BasicManager):

    def get_list(self, page, num=50, plan_id=None, title=None, start_date=None, end_date=None):
        result = self.values("id", "title", "desc", "plan_id", "created_at", "updated_at").order_by("-created_at")
        if plan_id:
            result = result.filter(plan_id=plan_id)
        if title:
            result = result.filter(title__contains=title)
        if start_date and end_date:
            result = result.filter(created_at__range=[start_date, end_date])
        paginator = Paginator(result, num)
        count = result.aggregate(count=Count('id'))
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage as e:
            return {'retlist': [], 'curpageidx': page,
                    'hasnext': False, 'hasprevious': False}
        return {'retlist': list(result_page), 'curpageidx': page, 'hasnext': result_page.has_next(),
                'hasprevious': result_page.has_previous(), 'total_page': paginator.num_pages, 'count': count['count']}


class ModelManagerPlanStageCourse(BasicManager):

    def get_list(self, page, num=50):
        result = self.values().order_by("-created_at")
        paginator = Paginator(result, num)
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage as e:
            return {'retlist': [], 'curpageidx': page,
                    'hasnext': False, 'hasprevious': False}
        return {'retlist': list(result_page), 'curpageidx': page, 'hasnext': result_page.has_next(),
                'hasprevious': result_page.has_previous(), 'total_page': paginator.num_pages}


class ModelManagerPlanJoinInfo(BasicManager):

    not_join_status = 0
    is_join_status = 1

    def get_list(self, page, num=50, plan_stage_id=None, phone=None, nickname=None, start_date=None, end_date=None, export=None):
        result = self.values("id", "plan_stage_id", "created_at", "student_id", "student__mobile", "student__nick_name", "plan__replenish") \
            .order_by("-created_at")
        student_id_list = list(result.values_list("student_id", flat=True))
        res1 = mm_PlanClockInInfo.filter(student_id__in=student_id_list).values("plan_stage_id", "student_id").annotate(clock_in_num=Count('student_id'))
        res2 = mm_PlanClockInInfo.filter(student_id__in=student_id_list, types=1).values("plan_stage_id", "student_id").annotate(replenish_clock_in_num=Count('student_id'))
        if plan_stage_id:
            result = result.filter(plan_stage_id=plan_stage_id)
            res1 = res1.filter(plan_stage_id=plan_stage_id)
            res2 = res2.filter(plan_stage_id=plan_stage_id)
        if phone:
            result = result.filter(student__mobile__contains=phone)
            res1 = res1.filter(student__mobile__contains=phone)
            res2 = res2.filter(student__mobile__contains=phone)
        if nickname:
            result = result.filter(student__nick_name__contains=nickname)
            res1 = res1.filter(student__nick_name__contains=nickname)
            res2 = res2.filter(student__nick_name__contains=nickname)
        if start_date and end_date:
            result = result.filter(created_at__range=[start_date, end_date])
            res1 = res1.filter(plan_stage__plan_join_info_ps__created_at__range=[start_date, end_date])
            res2 = res2.filter(plan_stage__plan_join_info_ps__created_at__range=[start_date, end_date])
        paginator = Paginator(result, num)
        count = result.aggregate(count=Count('id'))
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage as e:
            return {'retlist': [], 'curpageidx': page,
                    'hasnext': False, 'hasprevious': False}
        for item in result_page:
            item['clock_in_num'] = item['replenish_clock_in_num'] = 0
            for clock_in_info in res1:
                if item['student_id'] == clock_in_info['student_id'] and item['plan_stage_id'] == clock_in_info['plan_stage_id']:
                    item['clock_in_num'] = clock_in_info['clock_in_num']
            for replenish_clock_in_info in res2:
                if item['student_id'] == replenish_clock_in_info['student_id'] and item['plan_stage_id'] == replenish_clock_in_info['plan_stage_id']:
                    item['replenish_clock_in_num'] = replenish_clock_in_info['replenish_clock_in_num']
        if export:
            from io import BytesIO
            from django.http import HttpResponse
            import xlwt
            ws = xlwt.Workbook(encoding="UTF-8")
            w = ws.add_sheet(u'数据')
            w.write(0, 0, u'加入时间')
            w.write(0, 1, u'用户昵称')
            w.write(0, 2, u'手机号')
            w.write(0, 3, u'打卡次数')
            w.write(0, 4, u'补卡机会')
            excel_row = 1
            for obj in list(result_page):
                w.write(excel_row, 0, obj['created_at'].strftime('%Y-%m-%d'))
                w.write(excel_row, 1, obj['student__nick_name'])
                w.write(excel_row, 2, obj['student__mobile'])
                w.write(excel_row, 3, obj['clock_in_num'])
                w.write(excel_row, 4, int(obj['plan__replenish'])-int(obj['replenish_clock_in_num']))
                excel_row += 1
            sio = BytesIO()
            ws.save(sio)
            sio.seek(0)
            response = HttpResponse(sio.getvalue(), content_type='application/vnd.ms-excel')
            response['Content-Disposition'] = 'attachment; filename=stu_info.xls'
            response.write(sio.getvalue())
            return response
        return {'retlist': list(result_page), 'curpageidx': page, 'hasnext': result_page.has_next(),
                'hasprevious': result_page.has_previous(), 'total_page': paginator.num_pages, 'count': count['count']}

    def get_user_list(self, page, num=20, user_id=None):
        result = self.filter(plan__status=mm_Plan.on_line_status).annotate(title=F("plan__title"), counter=F("plan_stage__counter")).values("id", "plan_stage_id", "title", "counter", "created_at", "student_id").order_by("-created_at")
        if user_id:
            result = result.filter(student_id=user_id)
        paginator = Paginator(result, num)
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage as e:
            return {'retlist': [], 'curpageidx': page,
                    'hasnext': False, 'hasprevious': False}
        return {'retlist': list(result_page), 'curpageidx': page, 'hasnext': result_page.has_next(),
                'hasprevious': result_page.has_previous(), 'total_page': paginator.num_pages}

class ModelManagerPlanClockInInfo(BasicManager):

    normal_type = 0 #正常打卡
    replenish_type = 1 #补卡

    refine_status = 1 #精选

    clock_in_status_now_not = 1 #当天未打卡
    clock_in_status_before_not = 2 #之前未打卡
    clock_in_status_already = 3 #已打卡

    def get_list(self, page, num=50, plan_id=None, plan_stage_id=None, phone=None, nickname=None, course_title=None, start_date=None, end_date=None, export=None):
        result = self.values("id", "refine", "note_id", "note__created_at", "note__content", "plan_course__title", "student_id", "student__mobile", "student__nick_name", "plan_stage__desc") \
            .order_by("-created_at")
        if plan_id:
            result = result.filter(plan_id=plan_id)
        if plan_stage_id:
            result = result.filter(plan_stage_id=plan_stage_id)
        if phone:
            result = result.filter(student__mobile__contains=phone)
        if nickname:
            result = result.filter(student__nick_name__contains=nickname)
        if course_title:
            result = result.filter(plan_course__title__contains=course_title)
        if start_date and end_date:
            result = result.filter(note__created_at__range=[start_date, end_date])
        paginator = Paginator(result, num)
        count = result.aggregate(count=Count('id'))
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage as e:
            return {'retlist': [], 'curpageidx': page,
                    'hasnext': False, 'hasprevious': False}
        if export:
            from io import BytesIO
            from django.http import HttpResponse
            import xlwt
            ws = xlwt.Workbook(encoding="UTF-8")
            w = ws.add_sheet(u'数据')
            w.write(0, 0, u'加入时间')
            w.write(0, 1, u'用户昵称')
            w.write(0, 2, u'手机号')
            w.write(0, 3, u'打卡次数')
            w.write(0, 4, u'补卡机会')
            excel_row = 1
            for obj in list(result_page):
                w.write(excel_row, 0, obj['created_at'].strftime('%Y-%m-%d'))
                w.write(excel_row, 1, obj['student__nick_name'])
                w.write(excel_row, 2, obj['student__mobile'])
                w.write(excel_row, 3, obj['clock_in_num'])
                w.write(excel_row, 4, int(obj['plan__replenish'])-int(obj['replenish_clock_in_num']))
                excel_row += 1
            sio = BytesIO()
            ws.save(sio)
            sio.seek(0)
            response = HttpResponse(sio.getvalue(), content_type='application/vnd.ms-excel')
            response['Content-Disposition'] = 'attachment; filename=stu_info.xls'
            response.write(sio.getvalue())
            return response
        return {'retlist': list(result_page), 'curpageidx': page, 'hasnext': result_page.has_next(),
                'hasprevious': result_page.has_previous(), 'total_page': paginator.num_pages, 'count': count['count']}


class Note(models.Model):
    content = models.TextField(default='', blank=True)
    duration = models.IntegerField(default=0)
    imgs = models.TextField(default='', blank=True)
    student = models.ForeignKey(Student)
    status = models.PositiveSmallIntegerField(default=0)  # 0:公开  1：私密  2: 不展示
    types = models.PositiveSmallIntegerField(default=0)
    like = models.IntegerField(default=0)
    share = models.IntegerField(default=0)
    message = models.IntegerField(default=0)
    day = models.DateField(auto_now_add=True)
    poster = models.CharField(default='', max_length=250)
    private = models.PositiveSmallIntegerField(default=0)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    objects = ModelManagerNote()

    class Meta:
        verbose_name = ''
        verbose_name_plural = verbose_name
        db_table = "momself_note"


class NoteMessage(models.Model):
    note = models.ForeignKey(Note)
    content = models.CharField(max_length=200, default='')
    student = models.ForeignKey(Student)
    pid = models.IntegerField(default=0)
    status = models.PositiveSmallIntegerField(default=0)  # 默认0  1:已读
    types = models.PositiveSmallIntegerField(default=0)  # 0评论  1:点赞
    like = models.IntegerField(default=0)  # 针对评论的点赞
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    objects = ModelManagerNoteMessage()

    class Meta:
        verbose_name = ''
        verbose_name_plural = verbose_name
        db_table = "momself_note_message"


class NoteLike(models.Model):
    note = models.ForeignKey(Note)
    student = models.ForeignKey(Student)
    status = models.PositiveSmallIntegerField(default=0)  # 默认0  1:已读
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    objects = ModelManagerNoteLike()

    class Meta:
        verbose_name = ''
        verbose_name_plural = verbose_name
        db_table = "momself_note_like"


class FamousSentences(models.Model):
    name = models.CharField(max_length=50, default='')
    content_one = models.CharField(max_length=200)
    content_two = models.CharField(max_length=200)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    objects = ModelManagerFamousSentences()

    class Meta:
        verbose_name = ''
        verbose_name_plural = verbose_name
        db_table = "momself_famous_sentences"


class ShareParameter(models.Model):
    title = models.CharField(max_length=50)
    img = models.CharField(max_length=200)
    status = models.PositiveSmallIntegerField(default=0)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    objects = ModelManagerShareParameter()

    class Meta:
        verbose_name = ''
        verbose_name_plural = verbose_name
        db_table = "momself_share_parameter"


class AllData(models.Model):
    poster_num = models.IntegerField(default=0)
    poster_saoma_num = models.IntegerField(default=0)
    applet_share_num = models.IntegerField(default=0)
    workplace_silkbag_num = models.IntegerField(default=0)
    ranking_num = models.IntegerField(default=0)
    absorbed_number_num = models.IntegerField(default=0)
    absorbed_num = models.IntegerField(default=0)
    absorbed_biji_num = models.IntegerField(default=0)
    forward_num = models.IntegerField(default=0)
    comment_num = models.IntegerField(default=0)
    like_num = models.IntegerField(default=0)
    uv = models.IntegerField(default=0)
    pv = models.IntegerField(default=0)
    number_num = models.IntegerField(default=0)
    number_sum = models.IntegerField(default=0)
    day = models.DateField(auto_now_add=True, unique=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    objects = ModelManagerAllData()

    class Meta:
        verbose_name = ''
        verbose_name_plural = verbose_name
        db_table = "momself_all_data"


class NoteRanking(models.Model):
    duration = models.IntegerField(default=0)
    student = models.ForeignKey(Student, related_name='note_ranking')
    day = models.DateField(auto_now_add=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    objects = ModelManagerNoteRanking()

    class Meta:
        verbose_name = ''
        verbose_name_plural = verbose_name
        db_table = "momself_note_ranking"


class NoteWechatMessage(models.Model):
    priTmplId = models.CharField(max_length=100)
    title = models.CharField(max_length=50)
    content = models.CharField(max_length=200)
    example = models.CharField(max_length=200)
    types = models.PositiveSmallIntegerField(default=0)
    keywordEnumValueList = models.TextField(default='')
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    objects = ModelManagerNoteWechatMessage()

    class Meta:
        verbose_name = ''
        verbose_name_plural = verbose_name
        db_table = "momself_note_wechat_message"


class NoteServersMessage(models.Model):
    tmpl = models.ForeignKey(NoteWechatMessage)
    title = models.CharField(max_length=100, default='')
    rule = models.PositiveSmallIntegerField(default=1)  # 1:24小时  2:48小时  3:72小时
    push_sum = models.IntegerField(default=0)
    click_sum = models.IntegerField(default=0)
    today_pushsum = models.IntegerField(default=0)
    today_clicksum = models.IntegerField(default=0)
    status = models.PositiveSmallIntegerField(default=1)   # 1:开启  2:关闭
    url = models.CharField(default='', max_length=100)
    activity = models.CharField(default='', max_length=50)
    explain = models.CharField(default='', max_length=150)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    objects = ModelManagerNoteServersMessage()

    class Meta:
        verbose_name = ''
        verbose_name_plural = verbose_name
        db_table = "momself_note_servers_message"


class NoteBanner(models.Model):
    name = models.CharField(max_length=150, default='')
    img = models.CharField(max_length=200, default='')
    url = models.CharField(max_length=250, default='')
    sort = models.IntegerField(default=0)
    status = models.PositiveSmallIntegerField(default=0)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    objects = ModelManagerNoteBanner()

    class Meta:
        verbose_name = ''
        verbose_name_plural = verbose_name
        db_table = "momself_note_banner"


class Plan(models.Model):
    title = models.CharField(max_length=150, default='')
    desc = models.CharField(max_length=200, default='')
    cycle = models.IntegerField(default=0, verbose_name="周期")
    replenish = models.IntegerField(default=0, verbose_name="补卡次数")
    original_price = models.DecimalField(max_digits=8, decimal_places=2, default=0, verbose_name="原价")
    price = models.DecimalField(max_digits=8, decimal_places=2, default=0, verbose_name="售价")
    img = models.CharField(max_length=200, default='')
    bg_img = models.CharField(max_length=200, default='')
    virtual_num = models.IntegerField(default=0, verbose_name="虚拟人数")
    drainage_desc = models.CharField(max_length=200, default='', verbose_name='引流描述')
    code = models.CharField(max_length=200, default='', verbose_name='引流二维码')
    content = models.TextField(default='', blank=True)
    status = models.PositiveSmallIntegerField(default=0)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    objects = ModelManagerPlan()

    class Meta:
        verbose_name = ''
        verbose_name_plural = verbose_name
        db_table = "momself_Plan"


class PlanStage(models.Model):
    plan = models.ForeignKey(Plan, related_name='plan_stage_p')
    counter = models.IntegerField(default=1, verbose_name="计数器")
    desc = models.CharField(max_length=200, default='')
    all_dates = models.CharField(max_length=200, default='')
    start_day = models.DateField(null=True)
    end_day = models.DateField(null=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    objects = ModelManagerPlanStage()

    class Meta:
        verbose_name = ''
        verbose_name_plural = verbose_name
        db_table = "momself_plan_stage"


class PlanCourse(models.Model):
    plan = models.ForeignKey(Plan, related_name='plan_course_p')
    title = models.CharField(max_length=150, default='')
    desc = models.CharField(max_length=200, default='')
    content = models.TextField(default='', blank=True)
    task = models.TextField(default='', blank=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    objects = ModelManagerPlanCourse()

    class Meta:
        verbose_name = ''
        verbose_name_plural = verbose_name
        db_table = "momself_plan_course"


class PlanStageCourse(models.Model):
    plan = models.ForeignKey(Plan, related_name='plan_stage_course_p')
    plan_stage = models.ForeignKey(PlanStage, on_delete=models.CASCADE, related_name='plan_stage_course_ps')
    plan_course = models.ForeignKey(PlanCourse, related_name='plan_stage_course_pc')
    day = models.DateField()
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    objects = ModelManagerPlanStageCourse()

    class Meta:
        verbose_name = ''
        verbose_name_plural = verbose_name
        db_table = "momself_plan_stage_course"


class PlanJoinInfo(models.Model):
    plan = models.ForeignKey(Plan, related_name='plan_join_info_p')
    plan_stage = models.ForeignKey(PlanStage, related_name='plan_join_info_ps')
    plan_stage_course = models.ForeignKey(PlanStageCourse, related_name='plan_join_info_psc')
    duration = models.IntegerField(default=0)
    student = models.ForeignKey(Student)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    objects = ModelManagerPlanJoinInfo()

    class Meta:
        verbose_name = ''
        verbose_name_plural = verbose_name
        db_table = "momself_plan_join_info"


class PlanClockInInfo(models.Model):
    plan = models.ForeignKey(Plan, related_name='plan_clock_in_info_p')
    plan_stage = models.ForeignKey(PlanStage, related_name='plan_clock_in_info_ps')
    plan_course = models.ForeignKey(PlanCourse, related_name='plan_clock_in_info_pc')
    plan_stage_course = models.ForeignKey(PlanStageCourse, related_name='plan_clock_in_info_psc')
    student = models.ForeignKey(Student)
    note = models.ForeignKey(Note, on_delete=models.CASCADE)
    types = models.PositiveSmallIntegerField(default=0)
    refine = models.PositiveSmallIntegerField(default=0)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    objects = ModelManagerPlanClockInInfo()

    class Meta:
        verbose_name = ''
        verbose_name_plural = verbose_name
        db_table = "momself_plan_clock_in_info"


mm_Note = Note.objects
mm_NoteMessage = NoteMessage.objects
mm_NoteLike = NoteLike.objects
mm_FamousSentences = FamousSentences.objects
mm_ShareParameter = ShareParameter.objects
mm_AllData = AllData.objects
mm_NoteRanking = NoteRanking.objects
mm_NoteWechatMessage = NoteWechatMessage.objects
mm_NoteServersMessage = NoteServersMessage.objects
mm_NoteBanner = NoteBanner.objects
mm_Plan = Plan.objects
mm_PlanStage = PlanStage.objects
mm_PlanJoinInfo = PlanJoinInfo.objects
mm_PlanCourse = PlanCourse.objects
mm_PlanStageCourse = PlanStageCourse.objects
mm_PlanClockInInfo = PlanClockInInfo.objects